package com.xb;

import com.xb.Entity.Customers;
import com.xb.Util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class QueryTest {
    @Test
    public void QueryInsert(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection02();
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            int num = runner.update(conn, sql, "蔡徐坤", "cxk@163.com", "2000-01-10");
            System.out.println("成功插入 " + num);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{

            JDBCUtils.closeConAndPs(conn, null);
        }

    }
    @Test
    public void QuerySelectById(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection02();
            String sql = "SELECT name,email,birth FROM customers where id =?";
            BeanHandler<Customers> beanHandler = new BeanHandler<>(Customers.class);
            Customers customers = runner.query(conn, sql, beanHandler, 4);
            System.out.println(customers);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{

            JDBCUtils.closeConAndPs(conn, null);
        }

    }

    @Test
    public void QuerySelect(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection02();
            String sql = "SELECT name,email,birth FROM customers where id <?";
            BeanListHandler<Customers> beanListHandler = new BeanListHandler<>(Customers.class);
            List<Customers> query = runner.query(conn, sql, beanListHandler, 20);
            query.forEach(System.out::println);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{

            JDBCUtils.closeConAndPs(conn, null);
        }

    }
    @Test
    public void QuerySelectCount(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection02();
            String sql = "SELECT count(*) FROM customers";
            ScalarHandler scalarHandler = new  ScalarHandler();
            Long query = (Long) runner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{

            JDBCUtils.closeConAndPs(conn, null);
        }

    }
    @Test
    public void QuerySelectByHandler(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection02();
            String sql = "select * from customers";
            //自定义返回类型
            ResultSetHandler<Customers> resultSetHandler = new ResultSetHandler<Customers>(){
                @Override
                public Customers handle(ResultSet resultSet) throws SQLException {
                    return null;
                }
            };
            Customers query = runner.query(conn, sql, resultSetHandler);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{

            JDBCUtils.closeConAndPs(conn, null);
        }

    }
}
